﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Public;

namespace DAL
{
    public class ChuyenKhauDAL
    {
        public static List<ChuyenKhauPublic> LoadChuyenKhauBySoCK(int SoChuyenKhau)
        {
            var ck = Common.db.ExecuteStoreQuery<ChuyenKhauPublic>(@"select * from ChuyenKhau where SoChuyenKhau=" + SoChuyenKhau + "and left(RIGHT('0' + cast(MaCK as varchar(8)),8),2) in(select MaPX from ThietLap)");
            return ck.ToList();
        }
        public static List<ChuyenKhauPublic> LoadChuyenKhauByMaNK(int MaNK)
        {
            var ck = Common.db.ExecuteStoreQuery<ChuyenKhauPublic>(@"select * from ChuyenKhau where MaNK="+MaNK);
            return ck.ToList();
        }
        public static int CountChuyenDiByYear(int firstyear,int lastyear)
        {
            var cd = Common.db.ExecuteStoreQuery<int>(@"select count(*) from ChuyenKhau where left(RIGHT('0' + cast(MaCK as varchar(8)),8),2) in (select MaPX from ThietLap) and year(NgayNhan)>="+firstyear+"and year(NgayNhan)<="+lastyear);
            return cd.FirstOrDefault();
        }
        public static int CountChuyenDenByYear(int firstyear, int lastyear)
        {
            var cden = Common.db.ExecuteStoreQuery<int>(@"select count(*) from ChuyenKhau where left(RIGHT('0' + cast(MaCK as varchar(8)),8),2) not in (select MaPX from ThietLap) and year(NgayNhan)>=" + firstyear + "and year(NgayNhan)<=" + lastyear);
            return cden.FirstOrDefault();
        }

        public static int LoadMaCDi()
        {
            var cdi = Common.db.ExecuteStoreQuery<int>(@"select ISNULL(Max(MaCK),0) from ChuyenKhau where left(RIGHT('0' + cast(MaCK as varchar(8)),8),2)in(select MaPX from ThietLap)");
            return cdi.FirstOrDefault();
        }
       
        public static int LoadMaCDen(int MaPX)
        {
            var cden = Common.db.ExecuteStoreQuery<int>(@"select ISNULL(Max(MaCK),0) from ChuyenKhau where left(RIGHT('0' + cast(MaCK as varchar(8)),8),2)=" + MaPX);
            return cden.FirstOrDefault();
        }
        public static void InsertChuyenDen(ChuyenKhauPublic _cden)
        {
            ChuyenKhau ck = new ChuyenKhau();
            ck.CanBoNoiChuyen = _cden.CanBoNoiChuyen;
            ck.CanBoNoiNhan = _cden.CanBoNoiNhan;
            ck.ChucVuNguoiChuyen = _cden.ChucVuNguoiChuyen;
            ck.ChucVuNguoiNhan = _cden.ChucVuNguoiNhan;
            ck.NguoiCap = _cden.NguoiCap;
            ck.NguoiNhan = _cden.NguoiNhan;

            ck.LyDoChuyen = _cden.LyDoChuyen;
            ck.MaCK = _cden.MaCK;
            ck.MaNK = _cden.MaNK;
            ck.NgayCap = _cden.NgayCap;
            ck.NgayNhan = _cden.NgayNhan;
            //ck.NgayChuyen = _cden.NgayChuyen;
            //ck.NgayDen = _cden.NgayDen;
            
            ck.NoiCap = _cden.NoiCap;
            ck.NoiChuyen = _cden.NoiChuyen;
            ck.SoChuyenKhau = _cden.SoChuyenKhau;
            ck.SoSoHoKhau = _cden.SoSoHoKhau;
            Common.db.AddToChuyenKhaus(ck);
            Common.db.SaveChanges();
        }
        public static int LoadSoChuyenKhau()
        {
            var ck = Common.db.ExecuteStoreQuery<int>(@"select ISNULL(Max(SoChuyenKhau),0) from ChuyenKhau where left(RIGHT('0' + cast(MaCK as varchar(8)),8),2) in (select MaPX from ThietLap)");
            return ck.FirstOrDefault(); 
        }
        //public static int LoadMaCK()
        //{
        //    var ck = Common.db.ExecuteStoreQuery<int>(@"select Max(MaCK) from ChuyenKhau");
        //    return ck.FirstOrDefault();
        //}
    }
}
